<?php

namespace App\Models\ZZMed;

use Illuminate\Support\Facades\DB;

class ZDept extends ZZMedBaseModel
{
    protected $table = 'z_dept';

    protected $guarded = ['id'];

    const CREATED_AT = 'create_at';

    const UPDATED_AT = 'update_at';

    public static function getAuthorizedDepartments($departmentId, $type = 0, $docId = 0, $companyId = 11)
    {

        $sql = " 
                SELECT DISTINCT b.company_id , 
                e.name AS company_name , 
                a.id AS dept_id , c.name AS dept_name ,
                 d.id AS hosp_id , d.name AS hosp_name , d.province_id , f.name AS province_name, 
                 d.id as center_id ,d.name as center_name
                FROM  z_dept a 
                LEFT JOIN z_dept_authorization as b ON b.auth_dept_id = a.id 
                LEFT JOIN z_t_dept c ON c.id = a.t_dept_id 
                LEFT JOIN z_hosp d ON d.id = a.hosp_id 
                LEFT JOIN z_company e ON e.id = b.company_id 
                LEFT JOIN z_location f
                ON f.id = d.province_id WHERE ( b.company_id= '$companyId' AND b.dept_id = '$departmentId' )";

        if ($type == 1) {
            // z_dept_authorization 授权不包括本院的科室 需要从z_company_dept判断
            $sql .= "  union ALL
                     SELECT DISTINCT b.company_id , 
                      e.name AS company_name , 
                      a.id AS dept_id , c.name AS dept_name ,
                     d.id AS hosp_id , d.name AS hosp_name , d.province_id , f.name AS province_name, 
                     d.id as center_id ,d.name as center_name
                    FROM  z_dept a 
                    left join z_company_dept  as b on b.dept_id = a.id
		  LEFT JOIN z_t_dept c ON c.id = a.t_dept_id 
                    LEFT JOIN z_hosp d ON d.id = a.hosp_id 
                    LEFT JOIN z_company e ON e.id = b.company_id 
                    LEFT JOIN z_location f
                    ON f.id = d.province_id WHERE  b.company_id= '$companyId'  and  a.id =  '$departmentId' ";
        }


        return DB::connection('zz_med_mysql')->select($sql);


    }


    /**
     *  获取科室名称
     * @param $deptId
     * @return mixed|string
     */
    public static function getDeptName($deptId)
    {
        return self::query()
                ->from('z_dept as d')
                ->selectRaw('e.name as dept_name')
                ->leftJoin('z_t_dept as e', 'e.id', '=', 'd.t_dept_id')
                ->where('d.id', $deptId)
                ->first()->dept_name ?? '';
    }
}